--Create and insert tracer
USE PORTLAND_PY
GO

EXEC sys.sp_posttracertoken @publication = 'PortPYPub'
Go



--commit time
USE Distribution
Go
SELECT Top 100 
Convert(varchar, publisher_commit, 1)as Date, CONVERT(VARCHAR(8),publisher_commit,108)as time,
parent_tracer_id, publisher_commit, distributor_commit, subscriber_commit, 
DateDiff(millisecond, publisher_commit, distributor_commit) as [Time Publisher --> distributor in Millisecond],
DateDiff(millisecond, distributor_commit, subscriber_commit) as [Time distributor -->Subscriber in Millisecond],
DateDiff(millisecond, publisher_commit, subscriber_commit) as [Total Time Publisher -->Subscriber in Millisecond]


FROM 
	MStracer_history as H, MStracer_tokens As B
where
H.parent_tracer_id=B.tracer_id
Order by parent_tracer_id desc

-- average daily


USE Distribution
Go
SELECT Top 100 
Convert(varchar, publisher_commit, 1)as Date, 
DateDiff(millisecond, publisher_commit, distributor_commit) as [Time Publisher --> distributor in Millisecond],
DateDiff(millisecond, distributor_commit, subscriber_commit) as [Time distributor -->Subscriber in Millisecond],
DateDiff(millisecond, publisher_commit, subscriber_commit) as [Total Time Publisher -->Subscriber in Millisecond]


FROM 
	MStracer_history as H, MStracer_tokens As B
where
H.parent_tracer_id=B.tracer_id
Order by parent_tracer_id desc



USE Distribution
Go
SELECT 
Convert(varchar, publisher_commit, 1)as Date, 
DateDiff(millisecond, publisher_commit, distributor_commit)) as [Time Publisher --> distributor in Millisecond]
FROM 
	MStracer_history as H, MStracer_tokens As B
where
H.parent_tracer_id=B.tracer_id

GROUP BY Convert(varchar, publisher_commit, 1), DateDiff(millisecond, publisher_commit, distributor_commit)

Order by Convert(varchar, publisher_commit, 1) desc










USE Distribution
Go

;WITH DelayByDate as
(
SELECT 
Convert(varchar, publisher_commit, 1)as Date, 
DateDiff(millisecond, publisher_commit, distributor_commit) as [Time Publisher],
DateDiff(millisecond, distributor_commit, subscriber_commit) as [Time distributor],
DateDiff(millisecond, publisher_commit, subscriber_commit) as [Total Time Publisher]
FROM 
	MStracer_history as H, MStracer_tokens As B
where
H.parent_tracer_id=B.tracer_id
)
Select [Date], SUM([Time Publisher]) as [SUM] from DelayByDate
group by [Date], [Time Publisher]